package Emailhandler;

import Databasehandler.DBConnector;
import java.io.File;
import java.io.FileNotFoundException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Scanner;
import Errorhandler.ErrorLogger;

/**
 * Genererer HTML email, der afsendes til kunde.
 *
 * @author Børge
 */
public class HTMLGenerator {
    private final File parent = new File(System.getProperty("user.dir"));
    private String[] recips = null;
    private ArrayList<String> reportsWithNulls = new ArrayList<String>();
    private ArrayList<String> reportsToSend;
    private String logDir = parent + File.separator+"log"+File.separator;
    private String logName = "HtmlGeneratorErrors.log";

//<editor-fold desc="Send Mail til Modtager(e)">
    /**
     * Genererer email til afsendelse
     *
     *
     */
    public void generateMail()
    {
        //Hvem skal have rapport: SubscriptionID: En funktion der henter subscription ud fra BatchID og subscribtionID
        //hent dem der skal have sendt mail ud fra subscriberID: en funktion der henter email ud fra alle nævnte id

        //Hent liste med modtagere
        DBConnector dbc = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        GmailMailer gm = new GmailMailer();
        getUnsentReport();
        ArrayList<String> unsentList = reportsToSend;
        int batchReportID = 0;

        
        ArrayList<String> tempARR = new ArrayList<String>();

        for(int i = 0; i<unsentList.size(); i++)
        {
            batchReportID = Integer.parseInt(unsentList.get(i).substring(0, unsentList.get(i).indexOf(",")));
            try {
                dbc = new DBConnector();
                pstmt = dbc.createPreparedStatement("SELECT orscb.Email " +
                        "FROM tbl_obsreportsubscriber orscb " +
                        "INNER JOIN tbl_obsreportsubscription orscp ON orscp.FK_OBSReportSubscriberID=orscb.OBSReportSubscriberID " +
                        "INNER JOIN tbl_batchreportsystem brs ON brs.BatchReportSystemID=orscp.FK_BatchReportSystemID " +
                        "INNER JOIN tbl_batchreport br ON br.FK_BatchReportSystemID=brs.BatchReportSystemID " +
                        "WHERE br.BatchReportID=?;");
                pstmt.setInt(1, batchReportID);
                rs = pstmt.executeQuery();
                while(rs.next())
                {
                    tempARR.add(rs.getString(1));
                }
                recips = tempARR.toArray(new String[tempARR.size()]);

                //Subject til email
                String subj = "Batchkørsels rapport";

                //Indhold til email
                String mailContent = generateHTML(Integer.parseInt(reportsToSend.get(i).substring(0, unsentList.get(i).indexOf(","))));


                try{
                    gm.sendMail(recips, subj, mailContent);
                    registerInTimeLog("Afsendt korrekt");
                    }
                catch(Exception ex){
                    registerInTimeLog(ex.getMessage());
                }

            } catch (Exception ex) {
                try {
                ErrorLogger.writeToErrorLog(logDir+logName,"FEJL: "+ex.getMessage()+"\n");
            } catch (Exception ex1){} // fejl hvis der ikke kan skrives til log kan ikke håndteres, SKRIV_TIL_OS_EVENT_LOG
            }
            finally{
                try {
                    dbc.closeConn();
                    pstmt.close();
                    rs.close();
                } catch (SQLException ex) {
                    try {
                ErrorLogger.writeToErrorLog(logDir+logName,"SQL Fejl: "+ex.getMessage()+"\n");
            } catch (Exception ex1){} // fejl hvis der ikke kan skrives til log kan ikke håndteres, SKRIV_TIL_OS_EVENT_LOG
                }
            }
            unsentList.remove(i);
            i=-1;
        }
    }
//</editor-fold>
    
//<editor-fold desc="Hent Template">

    /**
     * Indlæser hvilken template der skal anvendes til email
     *
     * @param templateName
     * @return String
     */
    private String readTemplate(String templateName)
    {
        File template = new File(parent+File.separator+"tools"+File.separator+"HTML"+File.separator+templateName+".HTML");
        Scanner in;
        String ret = "";
        try {
            in = new Scanner(template);
            
            while(in.hasNext())
            {
                    ret+=in.nextLine();
            }
        } catch (FileNotFoundException ex) {
            try {
                ErrorLogger.writeToErrorLog(logDir+logName,"Filen kan ikke findes: "+ex.getMessage()+"\n");
            } catch (Exception ex1){} // fejl hvis der ikke kan skrives til log kan ikke håndteres, SKRIV_TIL_OS_EVENT_LOG
        }

        return ret;
    }
    //</editor-fold>

//<editor-fold desc="Genererer HTML">
    /**
     * Genererer en rapport email i html udfra anvendt template
     *
     * @param batchReportID
     * @return String
     */
    private String generateHTML(int batchReportID)
    {
        String reportTemplate = readTemplate("html_email_basic_template");
        DBConnector dbc = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<String> reportARR = new ArrayList<String>();
        try
        {
            dbc = new DBConnector();
            pstmt = dbc.createPreparedStatement("SELECT * FROM tbl_batchreport WHERE batchReportID=?");
            pstmt.setInt(1, batchReportID);
            rs = pstmt.executeQuery();


            while(rs.next())
            {
                //Indskriv hentet rapportværdier i rapporthtml

                //Rapportnavn
                reportTemplate = reportTemplate.replace("{ReportName}", rs.getString(2));

                //Success eller ej
                if(rs.getBoolean(6))
                    reportTemplate = reportTemplate.replace("{Status}", "Gennemført");
                else
                    reportTemplate = reportTemplate.replace("{Status}", "Ej Gennemført");

                //Starttid
                //Dokumentation: http://www.roseindia.net/sql/sqldate/mysql-date-java.shtml
                Timestamp tsStart = rs.getTimestamp(4);
                reportTemplate = reportTemplate.replace("{StartTime}", reportDate(tsStart));
                //Sluttid
                Timestamp tsEnd = rs.getTimestamp(5);
                reportTemplate = reportTemplate.replace("{EndTime}", reportDate(tsEnd));
                //Forløbet tid
                reportTemplate = reportTemplate.replace("{TotalTime}", elapsedTime(tsEnd, tsStart));
                //Statusbesked
                reportTemplate = reportTemplate.replace("{Messages}", rs.getString(7));

                //DETALJER, se næste while løkke

                reportTemplate = reportTemplate.replace("{Details}", getDetailSet(batchReportID));
                
            }
        }
        catch (SQLException ex) {
            try {
                ErrorLogger.writeToErrorLog(logDir+logName,"SQL Fejl: "+ex.getMessage()+"\n");
            } catch (Exception ex1){} // fejl hvis der ikke kan skrives til log kan ikke håndteres, SKRIV_TIL_OS_EVENT_LOG
        }
        finally
        {
            try {
                rs.close();
                dbc.closeConn();
                pstmt.close();
            } catch (SQLException ex) {
                try {
                ErrorLogger.writeToErrorLog(logDir+logName,"SQL Fejl: "+ex.getMessage()+"\n");
            } catch (Exception ex1){} // fejl hvis der ikke kan skrives til log kan ikke håndteres, SKRIV_TIL_OS_EVENT_LOG
            }
        }

        //søg string igennem for danske bogstaver og substituerer dem med html
        //escaped char
        for(int i = 0; i<reportTemplate.length(); i++)
        {
            char c;

            c = reportTemplate.charAt(i);

            if(c == 'æ' || c == 'Æ')
            {
                reportTemplate = reportTemplate.replace(Character.toString(c), escapeHTML(Character.toString(c)));
            }
            if(c == 'ø' || c == 'Ø')
            {
                reportTemplate = reportTemplate.replace(Character.toString(c), escapeHTML(Character.toString(c)));
            }
            if(c == 'å' || c == 'Å')
            {
                reportTemplate = reportTemplate.replace(Character.toString(c), escapeHTML(Character.toString(c)));
            }
        }
        
        return reportTemplate;
    }

    /**
     * Kalkulerer forløbet tid det har taget for en rapport at blive færdig.
     *
     * @param end
     * @param start
     * @return String
     */
    private String elapsedTime(Timestamp end, Timestamp start)
    {
        Calendar calStart = Calendar.getInstance();
        calStart.setTime(start);
        Calendar calEnd = Calendar.getInstance();
        calEnd.setTime(end);
        Calendar d = Calendar.getInstance();
        d.setTimeInMillis((calEnd.getTimeInMillis()-calStart.getTimeInMillis()));

        //Dokumentation: http://stackoverflow.com/questions/175554/how-to-convert-milliseconds-into-human-readable-form
        return Long.toString((d.getTime().getTime()/86400000))+" dag(e), "+
                Long.toString(((d.getTime().getTime()/3600000))%24)+" time(r), "+
                Long.toString(((d.getTime().getTime()/60000))%60)+" minut(ter), "+
                Long.toString(((d.getTime().getTime()/1000))%60)+" sekund(er)";
    }

    /**
     * Formatterer database date til dansk datoformat
     *
     * @param ts
     * @return String
     */
    private String reportDate(Timestamp ts)
    {
        DateFormat formatter;
        
        formatter = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
        
        return formatter.format(ts);
    }

//</editor-fold>

//<editor-fold desc="Hent detaljesæt ud fra batchreportID">

    /**
     * Henter detaljesæt ud fra rapport id, og konverterer til HTML
     *
     * @param batchReportID
     * @return String
     */
    private String getDetailSet(int batchReportID)
    {
        String detailTemplate = "";
        DBConnector dbc = null;
        ResultSet detailRS = null;
        ArrayList<ArrayList<String>> dsARR = new ArrayList<ArrayList<String>>();
        try {
            //hent detaljesæt for rapport
             dbc = new DBConnector();


            PreparedStatement detailPstm = dbc.createPreparedStatement("SELECT * FROM tbl_batchreportdetailset WHERE FK_BatchReportID=?");
            detailPstm.setInt(1, batchReportID);
            detailRS = detailPstm.executeQuery();
            while (detailRS.next()) {

                ArrayList<String> tempARR = new ArrayList<String>();
                for(int i = 3; i<=detailRS.getMetaData().getColumnCount(); i++)
                {
                    tempARR.add(detailRS.getString(i));

                }
                dsARR.add(tempARR);
            }
        } catch (SQLException ex) {
            try {
                ErrorLogger.writeToErrorLog(logDir+logName,"SQL Fejl: "+ex.getMessage()+"\n");
            } catch (Exception ex1){} // fejl hvis der ikke kan skrives til log kan ikke håndteres, SKRIV_TIL_OS_EVENT_LOG
        }

        String holder1="";

        String detailSetHTML = "<table class=\"detailSet\">"+
                "<tr>"+"<td class=\"deatilSetName\" colspan=\"5\">"+
                "<span class=\"headerTXT\">{DetailSetName}</span>"+"</td>"+"</tr>"+
                "<tr>"+"<td class=\"colomnHeader\">Antal Opdateringer</td>"+"<td class=\"colomnHeader\">Antal Oprettelser</td>"
                +"<td class=\"colomnHeader\">Antal Sletninger</td>"+"<td class=\"colomnHeader\">Antal Fejl</td>"+
                "<td class=\"colomnHeader\">Antal overspringninger</td>"+"</tr>"+"<tr>"+"<td class=\"detailSetDetails\">{TotalUPD}</td>"
                + "<td class=\"detailSetDetails\">{TotalCRE}</td>"+"<td class=\"detailSetDetails\">{TotalDEL}</td>"
                + "<td class=\"detailSetDetails\">{TotalERR}</td>"
                +"<td class=\"detailSetDetails\">{TotalSKI}</td>"+"</tr>"+"</table><br>";

        for(ArrayList<String> arr : dsARR)
        {
            for(String s : recips)
            {
                if(s.contains("gmail"))
                    detailTemplate = readTemplate("defaultDetailsetSimple");
                else
                    detailTemplate = detailSetHTML;
            }
             //readTemplate("defaultDetailsetSimple");
            detailTemplate = detailTemplate.replace("{DetailSetName}", arr.get(0));
            detailTemplate = detailTemplate.replace("{TotalUPD}", arr.get(1));
            detailTemplate = detailTemplate.replace("{TotalCRE}", arr.get(2));
            detailTemplate = detailTemplate.replace("{TotalDEL}", arr.get(3));
            detailTemplate = detailTemplate.replace("{TotalERR}", arr.get(4));
            detailTemplate = detailTemplate.replace("{TotalSKI}", arr.get(5));

            holder1+=detailTemplate;
            detailTemplate="";
        }
        return holder1;
    }
//</editor-fold>

//<editor-fold desc="Hent ikke-sendte rapportID">
    /**
     * Indlæser alle usendte rapporter
     */
    private void getUnsentReport()
    {
        DBConnector dbc = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<String> unsArr = new ArrayList<String>();
        try {
            dbc = new DBConnector();
            stmt = dbc.createStatement();
            rs = stmt.executeQuery("SELECT batchReportID, OBSReportSubscriptionID, OBSReportSubscriberID, timeSent "
                    + "FROM view_current "
                    + "WHERE timeSent IS null "
                    + "AND BatchReportSystemID IS NOT null "
                    + "GROUP BY BatchReportSystemID;");

            while(rs.next())
            {
                    unsArr.add(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3));
            }

        } catch (SQLException ex) {
            try {
                ErrorLogger.writeToErrorLog(logDir+logName,"SQL Fejl: "+ex.getMessage()+"\n");
            } catch (Exception ex1){} // fejl hvis der ikke kan skrives til log kan ikke håndteres, SKRIV_TIL_OS_EVENT_LOG
        }
        finally
        {
            try {
                rs.close();
                dbc.closeConn();
                stmt.close();
            } catch (SQLException ex) {
                try {
                ErrorLogger.writeToErrorLog(logDir+logName,"SQL Fejl: "+ex.getMessage()+"\n");
            } catch (Exception ex1){} // fejl hvis der ikke kan skrives til log kan ikke håndteres, SKRIV_TIL_OS_EVENT_LOG
            }
        }

        //Sorterer de rapporter fra der ikke indeholder subscriber og/eller subscription
        for(int i = 0; i<unsArr.size();i++)
        {
            if(unsArr.get(i).contains("null"))
            {
                reportsWithNulls.add(unsArr.get(i));
                unsArr.remove(i);
                i=-1;
            }
        }

        reportsToSend = unsArr;        
    }
    //</editor-fold>

//<editor-fold desc="Registrerer til Sendlog">
    /**
     * Registrerer hvilke rapport der er blevet sendt, og ikke sendt i database.
     *
     * @param statusMessage
     */
    private void registerInTimeLog(String statusMessage)
{
    DBConnector dbc = null;
    PreparedStatement pstmt = null;
    Statement stmnt = null;
    try
    {

        dbc = new DBConnector();
        stmnt = dbc.createStatement();
        ResultSet rs = stmnt.executeQuery("SELECT FK_BatchReportID, FK_OBSReportSubscriptionID FROM tbl_obsreportsendlog");

        if(rs.next())
        {
            while(rs.next())
            {
                if(reportsToSend.size()>0);
                {
                    for(int i = 0; i<reportsToSend.size();i++)
                    {
                        String a = rs.getString(2);
                        String b = reportsToSend.get(i).substring(reportsToSend.get(i).indexOf(",")+1, reportsToSend.get(i).lastIndexOf(","));
                        String c = rs.getString(1);
                        String d = reportsToSend.get(i).substring(0, reportsToSend.get(i).indexOf(","));

                        if(Integer.parseInt(rs.getString(2)) !=
                        Integer.parseInt(reportsToSend.get(i).substring(reportsToSend.get(i).indexOf(",")+1, reportsToSend.get(i).lastIndexOf(",")))
                        && Integer.parseInt(rs.getString(1)) !=
                        Integer.parseInt(reportsToSend.get(i).substring(0, reportsToSend.get(i).indexOf(","))))
                        {
                            pstmt = dbc.createPreparedStatement("INSERT INTO tbl_obsreportsendlog(FK_OBSReportSubscriptionID, TimeSent, StatusMessage, FK_BatchReportID) VALUES (?, Now(), ?, ?);");
                            pstmt.setInt(1, Integer.parseInt(reportsToSend.get(i).substring(reportsToSend.get(i).indexOf(",")+1, reportsToSend.get(i).lastIndexOf(","))));
                            pstmt.setString(2, statusMessage);
                            pstmt.setInt(3, Integer.parseInt(reportsToSend.get(i).substring(0, reportsToSend.get(i).indexOf(","))));
                            pstmt.executeUpdate();
                        }
                    }
                    if(reportsWithNulls.size()>0)
                    {
                        for(int i = 0; i<reportsWithNulls.size();i++)
                        {
                            if(rs.getString(2).equals(Types.NULL) && Integer.parseInt(rs.getString(1)) ==
                            Integer.parseInt(reportsWithNulls.get(i).substring(0, reportsWithNulls.get(i).indexOf(","))))
                            {
                                pstmt = dbc.createPreparedStatement("INSERT INTO tbl_obsreportsendlog(FK_OBSReportSubscriptionID, StatusMessage, FK_BatchReportID) VALUES (?, ?, ?);");
                                if(reportsWithNulls.get(i).substring(reportsWithNulls.get(i).indexOf("null"), reportsWithNulls.get(i).indexOf("null")+4).equals("null"))
                                {
                                    pstmt.setNull(1, Types.NULL);
                                    pstmt.setString(2, "Der er ikke tilknyttet noget abonnement til denne kørsel.");
                                }
                                pstmt.setInt(3, Integer.parseInt(reportsWithNulls.get(i).substring(0, reportsWithNulls.get(i).indexOf(","))));
                                pstmt.executeUpdate();
                            }
                        }
                        reportsWithNulls.clear();
                    }
                }
            }
        }
        else
        {
            if(reportsToSend.size()>0);
            {
                for(int i = 0; i<reportsToSend.size();i++)
                {
                    pstmt = dbc.createPreparedStatement("INSERT INTO tbl_obsreportsendlog(FK_OBSReportSubscriptionID, TimeSent, StatusMessage, FK_BatchReportID) VALUES (?, Now(), ?, ?);");
                    pstmt.setInt(1, Integer.parseInt(reportsToSend.get(i).substring(reportsToSend.get(i).indexOf(",")+1, reportsToSend.get(i).lastIndexOf(","))));
                    pstmt.setString(2, statusMessage);
                    pstmt.setInt(3, Integer.parseInt(reportsToSend.get(i).substring(0, reportsToSend.get(i).indexOf(","))));
                    pstmt.executeUpdate();
                }

                if(reportsWithNulls.size()>0)
                {
                    for(int i = 0; i<reportsWithNulls.size();i++)
                    {

                        pstmt = dbc.createPreparedStatement("INSERT INTO tbl_obsreportsendlog(FK_OBSReportSubscriptionID, StatusMessage, FK_BatchReportID) VALUES (?, ?, ?);");

                        if(reportsWithNulls.get(i).substring(0, reportsWithNulls.get(i).indexOf(",")).equals("null"))
                        {
                            pstmt.setInt(1, Integer.parseInt(reportsWithNulls.get(i).substring(reportsWithNulls.get(i).indexOf(","))));
                            pstmt.setString(2, "Der er ikke tilknyttet nogle batchrapport til denne kunde.");
                            pstmt.setNull(3, Types.NULL);

                        }
                        if(reportsWithNulls.get(i).substring(reportsWithNulls.get(i).indexOf("null"), reportsWithNulls.get(i).indexOf("null")+4).equals("null"))
                        {
                            pstmt.setNull(1, Types.NULL);
                            pstmt.setString(2, "Der er ikke tilknyttet noget abonnement til denne kørsel.");
                            pstmt.setInt(3, Integer.parseInt(reportsWithNulls.get(i).substring(0, reportsWithNulls.get(i).indexOf(","))));
                        }
                        
                        pstmt.executeUpdate();
                    }
                    reportsWithNulls.clear();
                }
            }
        }
    }
    catch (SQLException ex) {
        try {
                ErrorLogger.writeToErrorLog(logDir+logName,"SQL Fejl: "+ex.getMessage()+"\n");
            } catch (Exception ex1){} // fejl hvis der ikke kan skrives til log kan ikke håndteres, SKRIV_TIL_OS_EVENT_LOG
    }
    finally
    {
        try {
                dbc.closeConn();
                stmnt.close();
                //pstmt.close();
        }
        catch (SQLException ex) {
            try {
                ErrorLogger.writeToErrorLog(logDir+logName,"SQL Fejl: "+ex.getMessage()+"\n");
            } catch (Exception ex1){} // fejl hvis der ikke kan skrives til log kan ikke håndteres, SKRIV_TIL_OS_EVENT_LOG
        }
    }
}
//</editor-fold>

//<editor-fold desc="Escape Chars in HTML">
    /**
     * Konverterer specielle karakterer til HTML specielle karakter.
     *
     * @param s
     * @return String
     */
    private String escapeHTML(String s) {
        //Dokumentation: http://java2s.com/Code/Java/Network-Protocol/EscapeHTMLspecialcharactersfromaString.htm

        //Metode anvendes hvis en string indeholder en char som HTML ikke kan læse/fejlfortolker
        StringBuilder sb = new StringBuilder();
        int n = s.length();
        for (int i = 0; i < n; i++) {
          char c = s.charAt(i);
          switch (c) {
          case '<':
            sb.append("&lt;");
            break;
          case '>':
            sb.append("&gt;");
            break;
          case '&':
            sb.append("&amp;");
            break;
          case '"':
            sb.append("&quot;");
            break;
          case 'à':
            sb.append("&agrave;");
            break;
          case 'À':
            sb.append("&Agrave;");
            break;
          case 'â':
            sb.append("&acirc;");
            break;
          case 'Â':
            sb.append("&Acirc;");
            break;
          case 'ä':
            sb.append("&auml;");
            break;
          case 'Ä':
            sb.append("&Auml;");
            break;
          case 'å':
            sb.append("&aring;");
            break;
          case 'Å':
            sb.append("&Aring;");
            break;
          case 'æ':
            sb.append("&aelig;");
            break;
          case 'Æ':
            sb.append("&AElig;");
            break;
          case 'ç':
            sb.append("&ccedil;");
            break;
          case 'Ç':
            sb.append("&Ccedil;");
            break;
          case 'é':
            sb.append("&eacute;");
            break;
          case 'É':
            sb.append("&Eacute;");
            break;
          case 'è':
            sb.append("&egrave;");
            break;
          case 'È':
            sb.append("&Egrave;");
            break;
          case 'ê':
            sb.append("&ecirc;");
            break;
          case 'Ê':
            sb.append("&Ecirc;");
            break;
          case 'ë':
            sb.append("&euml;");
            break;
          case 'Ë':
            sb.append("&Euml;");
            break;
          case 'ï':
            sb.append("&iuml;");
            break;
          case 'Ï':
            sb.append("&Iuml;");
            break;
          case 'ô':
            sb.append("&ocirc;");
            break;
          case 'Ô':
            sb.append("&Ocirc;");
            break;
          case 'ö':
            sb.append("&ouml;");
            break;
          case 'Ö':
            sb.append("&Ouml;");
            break;
          case 'ø':
            sb.append("&oslash;");
            break;
          case 'Ø':
            sb.append("&Oslash;");
            break;
          case 'ß':
            sb.append("&szlig;");
            break;
          case 'ù':
            sb.append("&ugrave;");
            break;
          case 'Ù':
            sb.append("&Ugrave;");
            break;
          case 'û':
            sb.append("&ucirc;");
            break;
          case 'Û':
            sb.append("&Ucirc;");
            break;
          case 'ü':
            sb.append("&uuml;");
            break;
          case 'Ü':
            sb.append("&Uuml;");
            break;
          case '®':
            sb.append("&reg;");
            break;
          case '©':
            sb.append("&copy;");
            break;
          case '€':
            sb.append("&euro;");
            break;
          case ' ':
            sb.append("&nbsp;");
            break;

          default:
            sb.append(c);
            break;
          }
        }
        return sb.toString();
  }
//</editor-fold>
}
